package cn.caipengbo.untils.jdbc;

import cn.caipengbo.demo.User;
import org.junit.After;
import org.junit.Before;
import org.testng.annotations.Test;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Created by Myth on 1/23/2017.
 */
public class DbUtilTest {

    private void print(Object obj) {
        if (obj instanceof List) {
            List list = (List) obj;
            for (Object o : list) {
                if (o instanceof Map) {
                    Map<String, Object> map = (Map<String, Object>) o;
                    Set<String> set = map.keySet();
                    for (String key : set) {
                        Object value = map.get(key);
                        System.out.print(key + ":" + value + "\t");
                    }
                    System.out.println();
                } else {
                    System.out.println(o);
                }
            }
            System.out.println("总共查询出数据数量是：" + list.size());
        } else {
            System.out.println(obj);
        }
    }
    @Before
    public void setUp() throws Exception {
        DbUtil.openConnection();
    }

    @After
    public void tearDown() throws Exception {
        DbUtil.closeConnection();
    }

    @Test
    public void openConnection() throws Exception {
        DbUtil.openConnection();
    }

    @Test
    public void closeConnection() throws Exception {
        DbUtil.closeConnection();
    }

    @Test
    public void queryMapList() throws Exception {
        DbUtil.openConnection();
        String sql = "SELECT id,username,password FROM tb_users where id > ?";
        List<Map<String, Object>> lists = DbUtil.queryMapList(sql, 5);
        print(lists);
        DbUtil.closeConnection();
    }


    @Test
    public void queryBeanList() throws Exception {
        String sql = "SELECT * FROM tb_users";
        List<User> emList = DbUtil.queryBeanList(sql,User.class);
        print(emList);
    }

    @Test
    public void queryBean() throws Exception {
        String sql = "SELECT * FROM tb_users WHERE id = ?";
        User user = DbUtil.queryBean(sql,User.class,5);
        System.out.println(user.toString());
    }

    @Test
    public void queryObjectList() throws Exception {
        String sql = "SELECT id FROM tb_users";
        List<Integer> id = DbUtil.queryObjectList(sql,Integer.class);
        for (Integer index : id) {
            System.out.println(Integer.toString(index));
        }
    }

    @Test
    public void execute() throws Exception {
        String sql = "delete from tb_users";
        DbUtil.execute(sql);
    }
    @Test
    public void executeAsBatch() throws Exception {
        List<String> sqls = new ArrayList<String>(100);

        for (int i = 0; i < 8; i++) {
            sqls.add(i, new String("INSERT INTO tb_users(username,password) VALUES('myth" + Integer.toString(i + 1) + "','123')"));
        }
        for(String sql :sqls){
            System.out.println(sql);
        }
        DbUtil.executeAsBatch(sqls);
    }
    @Test
    public void executeAsBatch2() throws Exception {
        DbUtil.openConnection();
        String sql = "INSERT INTO tb_users(username,password) VALUES(?,?)";
        DbUtil.executeAsBatch(sql, new Object[][]{{"test1", "345"}, {"test2", "345"}});
        DbUtil.closeConnection();
    }

}